-- Copyright (c) 2010, College Center for Library Automation

-- See the included Copyright.txt file for full license

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Vendors](
  [VendorId] [int] IDENTITY(1,1) NOT NULL,
  [Name] [varchar](100) NOT NULL,
  [CounterCompliant] [bit] NOT NULL CONSTRAINT [DF_Vendors_CounterCompliant]  DEFAULT ((0)),
  [DisplayName] [varchar](100) NULL,
  [Username] [varchar](50) NULL,
  [Password] [varchar](50) NULL,
  [Scraper] [varchar](50) NULL,
 CONSTRAINT [PK_Vendors] PRIMARY KEY CLUSTERED 
(
  [VendorId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Somehow related to old manual data entry system... sigh.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vendors', @level2type=N'COLUMN',@level2name=N'DisplayName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'If vendor has a universal username/password pair for all colleges it belongs here.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vendors', @level2type=N'COLUMN',@level2name=N'Username'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The name of perl script to run.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vendors', @level2type=N'COLUMN',@level2name=N'Scraper'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StatsTypes](
  [StatsTypeId] [tinyint] IDENTITY(1,1) NOT NULL,
  [StatsType] [varchar](50) NOT NULL,
 CONSTRAINT [PK_StatTypes] PRIMARY KEY CLUSTERED 
(
  [StatsTypeId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Colleges](
  [CollegeId] [int] IDENTITY(1,1) NOT NULL,
  [ShortName] [varchar](50) NOT NULL,
  [Name] [varchar](100) NOT NULL,
 CONSTRAINT [PK_Colleges] PRIMARY KEY CLUSTERED 
(
  [CollegeId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FilterTypes](
  [FilterTypeId] [tinyint] NOT NULL,
  [FilterType] [varchar](50) NOT NULL,
  [FilterTypeShort] [char](2) NOT NULL,
 CONSTRAINT [PK_FilterTypes] PRIMARY KEY CLUSTERED 
(
  [FilterTypeId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    John Nowlin
-- Create date: Sepptember 8, 2008
-- Description: Creates the YTD Comparison report date.
-- Pass in Current Year and Month as numbers, ie. 2008 8 if blank
-- defaults to year and month of previous month
-- Sums searches to the vendor level for the prior year month, current
-- year month, prior year ytd to month and current year ytd to month
-- =============================================
CREATE PROCEDURE [dbo].[GetYTDComparison]
  (@fyin int = 0, @monthin int = 0)
AS
BEGIN
  SET NOCOUNT ON;

-- used for the ugly report. creates four columns.
-- prior year month, current year month, prior ytd, current ytd

  DECLARE
    @fyprior char(4),
    @fycurrent char(4),
    @runmonth char(2),
    @runmonthprior smalldatetime,
    @runmonthcurrent smalldatetime,
    @fypriorstart smalldatetime,
    @fycurrentstart smalldatetime

  -- if month is not passed in use last month
  IF (@monthin = 0)
    BEGIN
    SET @runmonth = RIGHT('0' + RTRIM(CAST(MONTH(DATEADD(month, -1, GETDATE())) AS char(2))) ,2)
    END

  ELSE
    BEGIN
    SET @runmonth = RIGHT('0' + RTRIM(CAST(@monthin AS char(2))) ,2)
    END

  -- if fy not passed in use year based on current date
  IF (@fyin = 0)
    BEGIN

    IF (MONTH(DATEADD(month, -1, GETDATE())) < 7)
      BEGIN
      SET @fycurrent = CAST(YEAR(DATEADD(month, -1, GETDATE())) AS char(4))
      END

    ELSE
      BEGIN
      -- for jul-dec year plus 1
      SET @fycurrent = CAST(YEAR(DATEADD(month, -1, GETDATE())) + 1 AS char(4))
      END

    END

  ELSE
    BEGIN
    SET @fycurrent = CAST(@fyin AS char(4))
    END

  SET @fyprior = CONVERT(nchar(4), CONVERT(int, @fycurrent) -1);
  SET @fypriorstart   = CONVERT(DATETIME, CONVERT(nchar(4), CONVERT(int, @fyprior  ) -1)+'-07-01 00:00:00', 102)
  SET @fycurrentstart = CONVERT(DATETIME, CONVERT(nchar(4), CONVERT(int, @fycurrent) -1)+'-07-01 00:00:00', 102)

  SET @runmonthprior   = CONVERT(SMALLDATETIME, @fyprior  +'-'+@runmonth+'-01 00:00:00', 102)
  SET @runmonthcurrent = CONVERT(SMALLDATETIME, @fycurrent+'-'+@runmonth+'-01 00:00:00', 102)

  -- combine all the periods into one select statement
  SELECT
    Vendor,
    SW,
    StatsType, 
    (SELECT
      ISNULL(SUM(i.Quantity), 0) AS SumSearches
     FROM
      ReportStats i
     WHERE
      i.Month = @runmonthprior AND
      i.Vendor = o.Vendor AND
      i.SW = o.SW AND
      i.StatsType = o.StatsType
    ) AS PriorMonth,

    (SELECT
      ISNULL(SUM(i.Quantity), 0) AS SumSearches
     FROM
      ReportStats i
     WHERE
      i.Month = @runmonthcurrent AND
      i.Vendor = o.Vendor AND
      i.SW = o.SW AND
      i.StatsType = o.StatsType
    ) AS CurrentMonth,

    (SELECT
      ISNULL(SUM(i.Quantity), 0) AS SumSearches
     FROM
      ReportStats i
     WHERE
      i.Month BETWEEN @fypriorstart AND @runmonthprior AND
      i.Vendor = o.Vendor AND
      i.SW = o.SW AND
      i.StatsType = o.StatsType
    ) AS PriorYTD,

    (SELECT
      ISNULL(SUM(i.Quantity), 0) AS SumSearches
     FROM
      ReportStats i
     WHERE
      i.Month BETWEEN @fycurrentstart AND @runmonthcurrent AND
      i.Vendor = o.Vendor AND
      i.SW = o.SW AND
      i.StatsType = o.StatsType
    ) AS CurrentYTD
  FROM
    ReportStats AS o
  WHERE o.StatsTypeId > 1
  GROUP BY Vendor, SW, StatsType
  ORDER BY Vendor, SW, StatsType
  
END


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DataSources](
  [DataSourceId] [int] IDENTITY(1,1) NOT NULL,
  [DataSource] [nvarchar](400) NOT NULL,
  [AddDate] [smalldatetime] NULL CONSTRAINT [DF_DataSources_AddDate]  DEFAULT (getdate()),
  [DisplayDataSource] [nvarchar](400) NULL,
 CONSTRAINT [PK_DataSource] PRIMARY KEY CLUSTERED 
(
  [DataSourceId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataSources', @level2type=N'COLUMN',@level2name=N'DisplayDataSource'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DataSourceVendor](
  [DataSourceVendorId] [int] IDENTITY(1,1) NOT NULL,
  [VendorId] [int] NOT NULL,
  [DataSourceId] [int] NOT NULL,
  [Manual] [bit] NOT NULL CONSTRAINT [DF_DataSourceVendor_Manual]  DEFAULT ((0)),
  [AddDate] [smalldatetime] NULL CONSTRAINT [DF_DataSourceVendor_AddDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_DataSourceVendor] PRIMARY KEY CLUSTERED 
(
  [DataSourceVendorId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LoginInformation](
  [LoginInformationId] [int] IDENTITY(1,1) NOT NULL,
  [CollegeId] [int] NOT NULL,
  [VendorId] [int] NOT NULL,
  [UserName] [varchar](50) NULL,
  [Password] [varchar](50) NULL,
  [Option1] [varchar](100) NULL,
  [Option2] [varchar](100) NULL,
  [StartDate] [smalldatetime] NULL,
  [EndDate] [smalldatetime] NULL,
 CONSTRAINT [PK_CollegeVendor] PRIMARY KEY CLUSTERED 
(
  [LoginInformationId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FilterDates](
  [FilterDateId] [int] IDENTITY(1,1) NOT NULL,
  [DataSourceVendorId] [int] NOT NULL,
  [StartDate] [smalldatetime] NOT NULL CONSTRAINT [DF_ActiveDates_StartDate]  DEFAULT (CONVERT([smalldatetime],'1900-1-1',(102))),
  [EndDate] [smalldatetime] NOT NULL CONSTRAINT [DF_ActiveDates_EndDate]  DEFAULT (CONVERT([smalldatetime],'2050-1-1',(102))),
  [FilterTypeId] [tinyint] NOT NULL CONSTRAINT [DF_FilterDates_FilterTypeId]  DEFAULT ((0)),
  [AgentCount] [tinyint] NULL,
  [AddDate] [smalldatetime] NULL CONSTRAINT [DF_FilterDates_AddDate]  DEFAULT (getdate()),
  [AddByUser] [varchar](50) NULL,
 CONSTRAINT [PK_FilterDates] PRIMARY KEY CLUSTERED 
(
  [FilterDateId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CollegeDataSourceVendor](
  [CollegeDataSourceVendorId] [int] IDENTITY(1,1) NOT NULL,
  [CollegeId] [int] NOT NULL,
  [DataSourceVendorId] [int] NOT NULL,
  [AddDate] [smalldatetime] NULL CONSTRAINT [DF_CollegeDataSourceVendor_AddDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_CollegeDataSourceVendor] PRIMARY KEY CLUSTERED 
(
  [CollegeDataSourceVendorId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Joins colleges, data sources and vendors. Each statistics row points to this table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollegeDataSourceVendor'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Stats](
  [StatsId] [int] IDENTITY(1,1) NOT NULL,
  [CollegeDataSourceVendorId] [int] NOT NULL,
  [Month] [smalldatetime] NOT NULL,
  [Quantity] [int] NOT NULL,
  [StatsTypeId] [tinyint] NOT NULL,
  [AddDate] [smalldatetime] NOT NULL CONSTRAINT [DF_Statistics_AddDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_Stats] PRIMARY KEY CLUSTERED 
(
  [StatsId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ReportStats]
AS
SELECT     TOP (100) PERCENT dbo.Vendors.DisplayName AS Vendor, dbo.Colleges.ShortName AS College, dbo.DataSources.DisplayDataSource AS Source, 
                      dbo.Stats.Month, dbo.Stats.Quantity, 
                      ISNULL(CASE dbo.FilterTypes.FilterTypeShort WHEN 'MA' THEN 'A' + CAST(dbo.FilterDates.AgentCount AS varchar(2)) 
                      ELSE dbo.FilterTypes.FilterTypeShort END, '  ') AS SW, dbo.StatsTypes.StatsType, dbo.CollegeDataSourceVendor.CollegeId, dbo.Stats.StatsTypeId, 
                      dbo.Vendors.VendorId, dbo.DataSourceVendor.DataSourceVendorId
FROM         dbo.FilterTypes INNER JOIN
                      dbo.FilterDates ON dbo.FilterTypes.FilterTypeId = dbo.FilterDates.FilterTypeId RIGHT OUTER JOIN
                      dbo.DataSources INNER JOIN
                      dbo.CollegeDataSourceVendor INNER JOIN
                      dbo.Colleges ON dbo.CollegeDataSourceVendor.CollegeId = dbo.Colleges.CollegeId INNER JOIN
                      dbo.DataSourceVendor ON dbo.CollegeDataSourceVendor.DataSourceVendorId = dbo.DataSourceVendor.DataSourceVendorId INNER JOIN
                      dbo.Vendors ON dbo.DataSourceVendor.VendorId = dbo.Vendors.VendorId ON 
                      dbo.DataSources.DataSourceId = dbo.DataSourceVendor.DataSourceId INNER JOIN
                      dbo.Stats ON dbo.CollegeDataSourceVendor.CollegeDataSourceVendorId = dbo.Stats.CollegeDataSourceVendorId INNER JOIN
                      dbo.StatsTypes ON dbo.Stats.StatsTypeId = dbo.StatsTypes.StatsTypeId ON dbo.FilterDates.EndDate >= dbo.Stats.Month AND 
                      dbo.FilterDates.StartDate <= dbo.Stats.Month AND dbo.FilterDates.DataSourceVendorId = dbo.DataSourceVendor.DataSourceVendorId
WHERE     (NOT (ISNULL(dbo.FilterTypes.FilterTypeShort, '  ') = 'SU'))


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    John Nowlin
-- Create date: September 4, 2008
-- Description: Insert a new datasource
-- Requires a vendor name and a datasource name
-- Outputs DatasourceId and DatasourceVendorId
-- Returns a 0 for success or a 1 if vendor does not exist
-- =============================================
CREATE PROCEDURE [dbo].[InsertDatasource]

  (@Vendor varchar(100),
   @DataSource nvarchar(400),
   @Manual int,
   @DataSourceId int OUTPUT,
   @DataSourceVendorId int OUTPUT)

AS

  DECLARE
    @myError int,
    @myRowcount int
  
  DECLARE
    @VendorId int
    
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  /*
    lookup vendor, insert new datasoruce and join them in the datasourcevendor gerund
  */
  BEGIN TRAN

  -- check for existing datasource
  SELECT @VendorId = VendorId
    FROM  Vendors
    WHERE Name = @Vendor

  IF @@ROWCOUNT = 0
    BEGIN

    -- vendor does not exist. this procedure does not add vendors.
    SET @myError = 1
    PRINT '#error: vendor does not exist. add vendor first'
    GOTO Handle_Error

    END

  -- check for existing datasource
  SELECT @DataSourceId = DataSourceId
    FROM  DataSources
    WHERE DataSource = @DataSource

  IF @@ROWCOUNT = 0
    BEGIN

    -- does not exist, insert datasource 
    INSERT
      INTO DataSources(DataSource, DisplayDataSource)
      VALUES (@DataSource, @DataSource)

    SELECT @DataSourceId = IDENT_CURRENT('DataSources')

    END

-- check for existing datasourcevendor
-- if we are really adding a new datasource this should not exist
  SELECT @DataSourceVendorId = DataSourceVendorId
    FROM  DataSourceVendor
    WHERE DataSourceId = @DataSourceId AND VendorId = @VendorId

  IF @@ROWCOUNT = 0
    BEGIN

    -- insert a matching datasourcevendor row
    INSERT
      INTO DataSourceVendor (DataSourceId, VendorId, Manual)
      VALUES (@DataSourceId, @VendorId, @Manual)

    SELECT @DataSourceVendorId = IDENT_CURRENT('DataSourceVendor')

    END

-- check for existing filterdate
-- if we are really adding a new datasource this should not exist
  DECLARE @FilterDateId int

  SELECT @FilterDateId = FilterDateId
    FROM  FilterDates
    WHERE DataSourceVendorId = @DataSourceVendorId

  IF @@ROWCOUNT = 0
    BEGIN

    -- insert a matching filter row set to AA auto added
    INSERT
      INTO FilterDates (DataSourceVendorId, FilterTypeId)
      VALUES (@DataSourceVendorId, 4)

    END

  COMMIT TRAN
  RETURN 0

Handle_Error:
  ROLLBACK TRAN
  RETURN @myError


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_DataSourceVendor]
AS
SELECT     TOP (100) PERCENT dbo.DataSourceVendor.DataSourceVendorId, dbo.Vendors.Name, dbo.DataSources.DataSource, 
                      dbo.Vendors.Name + ' ' + dbo.DataSources.DataSource AS Source, dbo.DataSourceVendor.Manual, dbo.DataSourceVendor.VendorId, 
                      dbo.DataSources.AddDate
FROM         dbo.DataSources INNER JOIN
                      dbo.DataSourceVendor ON dbo.DataSources.DataSourceId = dbo.DataSourceVendor.DataSourceId INNER JOIN
                      dbo.Vendors ON dbo.DataSourceVendor.VendorId = dbo.Vendors.VendorId



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_LoginInformation]
AS
SELECT     TOP (100) PERCENT dbo.Vendors.Name, dbo.LoginInformation.VendorId, dbo.Colleges.ShortName, dbo.LoginInformation.CollegeId, 
                      dbo.LoginInformation.UserName, dbo.LoginInformation.Password, dbo.LoginInformation.Option1, dbo.LoginInformation.Option2, 
                      dbo.Vendors.Username AS VendorUsername, dbo.Vendors.Password AS VendorPassowrd, dbo.LoginInformation.LoginInformationId, 
                      dbo.LoginInformation.StartDate, dbo.LoginInformation.EndDate
FROM         dbo.LoginInformation INNER JOIN
                      dbo.Colleges ON dbo.LoginInformation.CollegeId = dbo.Colleges.CollegeId INNER JOIN
                      dbo.Vendors ON dbo.LoginInformation.VendorId = dbo.Vendors.VendorId
ORDER BY dbo.Vendors.Name, dbo.Colleges.ShortName


GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_LoginInformation'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    John Nowlin
-- Create date: August 4, 2008
-- Description: Insert Stats information
-- =============================================
CREATE PROCEDURE [dbo].[InsertStats]
  -- Add the parameters for the stored procedure here
  (@VendorId int,
   @CollegeId int,
   @DataSource nvarchar(400),
   @MonthIn char(10),
   @Quantity int,
   @StatsTypeId tinyint)
AS

BEGIN

  DECLARE @DataSourceId int,
    @DataSourceVendorId int,
    @CollegeDataSourceVendorId int,
    @Month smalldatetime,
    @StatsId Int,
    @OldQuantity Int
    
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  -- convert character month yyyy.mm.dd to smalldatetime
  SET @Month = CONVERT(smalldatetime, @MonthIn, 102)

  /*
    use datasource, vendorid and collegeid to get collegedatasourcevendorid
    lookup up each table and gerund along the way. first datasource, then datasourcevendor, then collegedatasourcevendor.
    add any missing parts if need be.
    */
  BEGIN

    -- get the datasource
    SELECT @DataSourceId = DataSourceId
      FROM  DataSources
      WHERE DataSource = @DataSource

    IF @@ROWCOUNT = 0
      BEGIN
    
      -- does not exist, insert datasource incase vendor has added something new
      INSERT
        INTO DataSources(DataSource, DisplayDataSource)
        VALUES (@DataSource, @DataSource)

      SELECT @DataSourceId = IDENT_CURRENT('DataSources')
      
      END

    -- get the datasourcevendor 
    SELECT @DataSourceVendorId = DataSourceVendorId
      FROM  DataSourceVendor
      WHERE DataSourceId = @DataSourceId AND VendorId = @VendorId

    IF @@ROWCOUNT = 0
      BEGIN
    
      -- insert a matching datasourcevendor row
      INSERT
        INTO DataSourceVendor (DataSourceId, VendorId)
        VALUES (@DataSourceId, @VendorId)

      SELECT @DataSourceVendorId = IDENT_CURRENT('DataSourceVendor')
      
      END

  -- check for existing filterdate
    DECLARE @FilterDateId int

    SELECT @FilterDateId = FilterDateId
      FROM  FilterDates
    WHERE DataSourceVendorId = @DataSourceVendorId

  IF @@ROWCOUNT = 0
    BEGIN

    -- insert a matching filter row
    INSERT
      INTO FilterDates (DataSourceVendorId, FilterTypeId)
      VALUES (@DataSourceVendorId, 4)

    END

    -- get the college datasource vendor id
    SELECT @CollegeDataSourceVendorId = CollegeDataSourceVendorId
      FROM  CollegeDataSourceVendor
      WHERE CollegeId = @CollegeId AND DataSourceVendorId = @DataSourceVendorId

    IF @@ROWCOUNT = 0
      BEGIN
    
      -- insert a matching collegedatasourcevendor row
      INSERT
        INTO CollegeDataSourceVendor (CollegeId, DataSourceVendorId)
        VALUES (@CollegeId, @DataSourceVendorId)

      SELECT @CollegeDataSourceVendorId = IDENT_CURRENT('CollegeDataSourceVendor')
      
      END

  END
  
  -- see if there is an existing Stats row. 
  -- if no row insert a new one
  -- if so check the quantity matches
  BEGIN

    SELECT
      @StatsId = StatsId,
      @OldQuantity = Quantity
    FROM Stats
    WHERE CollegeDataSourceVendorID = @CollegeDataSourceVendorID
      AND Month = @Month AND StatsTypeId = @StatsTypeId

    -- if no row, insert a new one
    IF @@ROWCOUNT = 0
      BEGIN
    
      IF (@Quantity > 0)
        BEGIN

        INSERT INTO Stats (
          CollegeDataSourceVendorID,
          Month,
          Quantity,
          StatsTypeId)
        VALUES (
          @CollegeDataSourceVendorID,
          @Month, 
          @Quantity,
          @StatsTypeId)
        END
  
      END
      
    ELSE
      BEGIN

      IF (@Quantity = 0)
        BEGIN

        DELETE Stats
        WHERE StatsId = @StatsId

        END

      ELSE IF NOT ( @OldQuantity = @Quantity)
        
        BEGIN

        UPDATE Stats
        SET Quantity = @Quantity
        WHERE StatsId = @StatsId

        END

      END

  END

END


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    John Nowlin
-- Create date: August 4, 2008
-- Description: Warning, will delete a datasource and all stats
-- that go with it.
-- =============================================
CREATE  PROCEDURE [dbo].[DeleteDatasource]
  (@VendorId int,
   @DataSource nvarchar(400))

AS

BEGIN

  DECLARE @DataSourceId int,
    @DataSourceVendorId int,
    @CollegeDataSourceVendorId int,
    @DSVRowcount int,
    @MaxRows int,
    @RowCnt int


  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  /*
    use datasource, vendorid and collegeid to get collegedatasourcevendorid
    lookup up each table and gerund along the way. first datasource, then datasourcevendor, then collegedatasourcevendor.
    add any missing parts if need be.
    */

  -- get the datasource
  SELECT @DataSourceId = DataSourceId
    FROM  DataSources
    WHERE DataSource = @DataSource

  IF @@ROWCOUNT = 0
  BEGIN

    PRINT 'Could not find ' + @Datasource
    RETURN 0

  END

  -- get the datasourcevendor 
  SELECT @DataSourceVendorId = DataSourceVendorId
    FROM  DataSourceVendor
    WHERE DataSourceId = @DataSourceId AND VendorId = @VendorId

  SET @DSVRowcount = @@ROWCOUNT

  IF @DSVRowcount = 0
  BEGIN

    -- check for multiple vendor usage
    SELECT @DataSourceVendorId = DataSourceVendorId
      FROM  DataSourceVendor
      WHERE DataSourceId = @DataSourceId
    
    SET @DSVRowcount = @@ROWCOUNT

    IF @DSVRowcount = 0
    BEGIN

      DELETE Datasources WHERE DataSourceId = @DataSourceId
      PRINT 'Orphan datasource is deleted'
    END
 
    RETURN 0
 
  END

  -- get all the college datasource vendor ids
  DECLARE @CDSV_Ids TABLE 
   (rownum int IDENTITY (1, 1) Primary key NOT NULL,
    CollegeDataSourceVendorId int not null)

  INSERT INTO @CDSV_Ids 
    (CollegeDataSourceVendorId)

    SELECT CollegeDataSourceVendorId
    FROM  CollegeDataSourceVendor
    WHERE DataSourceVendorId = @DataSourceVendorId

  SET @MaxRows = @@ROWCOUNT
  IF @MaxRows = 0
  BEGIN

    DELETE DataSourceVendor WHERE DataSourceVendorId = @DataSourceVendorId

    IF @DSVRowcount = 1
    BEGIN

      DELETE Datasources WHERE DataSourceId = @DataSourceId

    END

    RETURN 0

  END

  SET @RowCnt = 1

  WHILE @RowCnt <= @MaxRows
  BEGIN

    SELECT @CollegeDataSourceVendorId = CollegeDataSourceVendorId
    FROM @CDSV_Ids
     WHERE rownum = @RowCnt

     DELETE Stats
       WHERE CollegeDataSourceVendorId = @CollegeDataSourceVendorId

     DELETE CollegeDataSourceVendor
       WHERE CollegeDataSourceVendorId = @CollegeDataSourceVendorId

    print @CollegeDataSourceVendorId
    SET @RowCnt = @RowCnt + 1

  END

  DELETE DataSourceVendor WHERE DataSourceVendorId = @DataSourceVendorId

  IF @DSVRowcount = 1
  BEGIN

    DELETE Datasources WHERE DataSourceId = @DataSourceId

  END

END


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    John Nowlin
-- Create date: August 4, 2008
-- Description: Warning, will delete a datasource and all stats
-- that go with it.
-- =============================================
CREATE  PROCEDURE [dbo].[DeleteDataSourceVendorId]
  (@DataSourceVendorId int)

AS

BEGIN

  DECLARE @DataSourceId int,
    @VendorId int,
    @CollegeDataSourceVendorId int,
    @DSVRowcount int,
    @MaxRows int,
    @RowCnt int


  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  /*
    use datasourcevendorid and collegeid to get collegedatasourcevendorid
    lookup up each table and gerund along the way. first datasource, then datasourcevendor, then collegedatasourcevendor.
    add any missing parts if need be.
    */

  -- get the datasource
  SELECT @DataSourceId = DataSourceId
    FROM  DataSourceVendor
    WHERE DataSourceVendorId = @DataSourceVendorId

  IF @@ROWCOUNT = 0
  BEGIN

    PRINT 'Could not find ' + @DatasourceVendorId
    RETURN 0

  END

  -- check for multiple vendor usage
  SELECT @DataSourceVendorId = DataSourceVendorId
    FROM  DataSourceVendor
    WHERE DataSourceId = @DataSourceId

  SET @DSVRowcount = @@ROWCOUNT

  -- get all the college datasource vendor ids
  DECLARE @CDSV_Ids TABLE 
   (rownum int IDENTITY (1, 1) Primary key NOT NULL,
    CollegeDataSourceVendorId int not null)

  INSERT INTO @CDSV_Ids 
    (CollegeDataSourceVendorId)

    SELECT CollegeDataSourceVendorId
    FROM  CollegeDataSourceVendor
    WHERE DataSourceVendorId = @DataSourceVendorId

  SET @MaxRows = @@ROWCOUNT
  IF @MaxRows > 0
  BEGIN

    SET @RowCnt = 1

    WHILE @RowCnt <= @MaxRows
    BEGIN

      SELECT @CollegeDataSourceVendorId = CollegeDataSourceVendorId
      FROM @CDSV_Ids
        WHERE rownum = @RowCnt

      DELETE Stats
        WHERE CollegeDataSourceVendorId = @CollegeDataSourceVendorId

      DELETE CollegeDataSourceVendor
        WHERE CollegeDataSourceVendorId = @CollegeDataSourceVendorId

      SET @RowCnt = @RowCnt + 1

    END

    DELETE DataSourceVendor WHERE DataSourceVendorId = @DataSourceVendorId

    IF @DSVRowcount = 1
    BEGIN

      DELETE Datasources WHERE DataSourceId = @DataSourceId

    END
  END
END


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    John Nowlin
-- Create date: August 4, 2008
-- Description: Update Stats information
-- =============================================
CREATE  PROCEDURE [dbo].[ManualEntry]
  (@CollegeId int,
   @DataSourceVendorId int,
   @MonthIn char(10),
   @StatsType tinyint,
   @Stats int)
AS

BEGIN

  DECLARE
    @CollegeDataSourceVendorId int,
    @Month smalldatetime,
    @StatsId Int,
    @OldStats Int
    
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  -- get the college datasource vendor id
  SELECT @CollegeDataSourceVendorId = CollegeDataSourceVendorId
    FROM  CollegeDataSourceVendor
    WHERE CollegeId = @CollegeId AND DataSourceVendorId = @DataSourceVendorId

  IF @@ROWCOUNT = 0
    BEGIN

    -- insert a matching collegedatasourcevendor row
    INSERT
    INTO CollegeDataSourceVendor (CollegeId, DataSourceVendorId)
    VALUES (@CollegeId, @DataSourceVendorId)

    SELECT @CollegeDataSourceVendorId = IDENT_CURRENT('CollegeDataSourceVendor')
    
    END

  -- convert character month yyyy.mm.dd to smalldatetime
  SET @Month = CONVERT(smalldatetime, @MonthIn, 102)

  -- see if there is an existing Stats row. if so check the quantity, must report quantitiy changes
  -- if not row insert a new one
  BEGIN

    SELECT
      @StatsId = StatsId,
      @OldStats = Quantity
    FROM Stats
    WHERE CollegeDataSourceVendorID = @CollegeDataSourceVendorID
      AND Month = @Month
      AND StatsTypeId = @StatsType

    -- if no row, insert a new one
    IF @@ROWCOUNT = 0
      BEGIN
    
      IF @Stats > 0
        BEGIN

        INSERT INTO Stats (
          CollegeDataSourceVendorID,
          Month,
          Quantity,
          StatsTypeId)
        VALUES (
          @CollegeDataSourceVendorID,
          @Month,  
          @Stats,
          @StatsType)
        END
      END
      
    ELSE
      BEGIN

      IF @Stats = 0
        BEGIN
        DELETE Stats
        WHERE StatsId = @StatsId

        END

      ELSE IF NOT @OldStats = @Stats
        
        BEGIN

        UPDATE Stats
        SET Quantity = @Stats
        WHERE StatsId = @StatsId

        END

    END

  END

END


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_Probable_Missing_Data]
AS
SELECT     TOP (100) PERCENT dbo.View_DataSourceVendor.Source, dbo.StatsTypes.StatsType,
                          (SELECT     SUM(Stats_1.Quantity) AS Expr1
                            FROM          dbo.Stats AS Stats_1 INNER JOIN
                                                   dbo.CollegeDataSourceVendor AS CollegeDataSourceVendor_1 ON 
                                                   Stats_1.CollegeDataSourceVendorId = CollegeDataSourceVendor_1.CollegeDataSourceVendorId
                            WHERE      (dbo.CollegeDataSourceVendor.DataSourceVendorId = CollegeDataSourceVendor_1.DataSourceVendorId) AND 
                                                   (Stats_1.StatsTypeId = dbo.Stats.StatsTypeId) AND (Stats_1.Month = DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 2, GETDATE())), 0))
                            GROUP BY Stats_1.StatsTypeId, CollegeDataSourceVendor_1.DataSourceVendorId
                            HAVING      (Stats_1.StatsTypeId > 1)) AS Quantity2MonthsBack
FROM         dbo.Stats INNER JOIN
                      dbo.CollegeDataSourceVendor ON dbo.Stats.CollegeDataSourceVendorId = dbo.CollegeDataSourceVendor.CollegeDataSourceVendorId INNER JOIN
                      dbo.View_DataSourceVendor ON dbo.CollegeDataSourceVendor.DataSourceVendorId = dbo.View_DataSourceVendor.DataSourceVendorId INNER JOIN
                      dbo.StatsTypes ON dbo.Stats.StatsTypeId = dbo.StatsTypes.StatsTypeId
WHERE     (dbo.Stats.StatsTypeId > 1) AND (dbo.Stats.Month = DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 2, GETDATE())), 0))
GROUP BY dbo.View_DataSourceVendor.Source, dbo.StatsTypes.StatsType, dbo.Stats.StatsTypeId, dbo.CollegeDataSourceVendor.DataSourceVendorId
HAVING      ((SELECT     SUM(Stats_2.Quantity) AS Expr1
                         FROM         dbo.Stats AS Stats_2 INNER JOIN
                                                dbo.CollegeDataSourceVendor AS CollegeDataSourceVendor_2 ON 
                                                Stats_2.CollegeDataSourceVendorId = CollegeDataSourceVendor_2.CollegeDataSourceVendorId
                         WHERE     (dbo.CollegeDataSourceVendor.DataSourceVendorId = CollegeDataSourceVendor_2.DataSourceVendorId) AND 
                                                (Stats_2.StatsTypeId = dbo.Stats.StatsTypeId) AND (Stats_2.Month = DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 1, GETDATE())), 0))
                         GROUP BY Stats_2.StatsTypeId, CollegeDataSourceVendor_2.DataSourceVendorId
                         HAVING      (Stats_2.StatsTypeId > 1)) IS NULL)
ORDER BY dbo.View_DataSourceVendor.Source


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    john nowlin
-- Create date: 3/3/2009
-- Description: gets data from run month and previous month and looks 
--  for datasources with data from previous month that has no data in 
--  run month. presumably something is missing that needs to be investigated.
--  run month is passed in and if blank set to current date.
-- =============================================
CREATE PROCEDURE [dbo].[Get_Probable_Missing_Data]
  -- format yyyy-mm-dd
  (@monthin as varchar(10))
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  DECLARE
    @runmonth as smalldatetime, 
    @prevmonth as smalldatetime

  IF (@monthin = '')
    BEGIN
    SET @runmonth = GETDATE()
    END
  ELSE
    BEGIN
    SET @runmonth = CONVERT(smalldatetime, @MonthIn, 102)
    END

-- set runmonth to first day of month
  SET @runmonth = DATEADD(dd, - DAY(@runmonth) + 1, @runmonth)
  SET @prevmonth = DATEADD(mm, - 1, @runmonth)
  
-- compares two selects, one for this month and one for previous month
  SELECT     TOP (100) PERCENT dbo.View_DataSourceVendor.Source, dbo.StatsTypes.StatsType,
    (SELECT     SUM(Stats_1.Quantity) AS Expr1
    FROM          dbo.Stats AS Stats_1 INNER JOIN
    dbo.CollegeDataSourceVendor AS CollegeDataSourceVendor_1 ON 
    Stats_1.CollegeDataSourceVendorId = CollegeDataSourceVendor_1.CollegeDataSourceVendorId
    WHERE      (dbo.CollegeDataSourceVendor.DataSourceVendorId = CollegeDataSourceVendor_1.DataSourceVendorId) AND 
    (Stats_1.StatsTypeId = dbo.Stats.StatsTypeId) AND (Stats_1.Month = @prevmonth)
    GROUP BY Stats_1.StatsTypeId, CollegeDataSourceVendor_1.DataSourceVendorId
    HAVING      (Stats_1.StatsTypeId > 1)) AS Quantity2MonthsBack

  FROM         dbo.Stats INNER JOIN
    dbo.CollegeDataSourceVendor ON dbo.Stats.CollegeDataSourceVendorId = dbo.CollegeDataSourceVendor.CollegeDataSourceVendorId INNER JOIN
    dbo.View_DataSourceVendor ON dbo.CollegeDataSourceVendor.DataSourceVendorId = dbo.View_DataSourceVendor.DataSourceVendorId INNER JOIN
    dbo.StatsTypes ON dbo.Stats.StatsTypeId = dbo.StatsTypes.StatsTypeId

  WHERE
    (dbo.Stats.StatsTypeId > 1) AND (dbo.Stats.Month = @prevmonth)

  GROUP BY
    dbo.View_DataSourceVendor.Source, dbo.StatsTypes.StatsType, dbo.Stats.StatsTypeId, dbo.CollegeDataSourceVendor.DataSourceVendorId

  HAVING
    ((SELECT     SUM(Stats_2.Quantity) AS Expr1
      FROM         dbo.Stats AS Stats_2 INNER JOIN
      dbo.CollegeDataSourceVendor AS CollegeDataSourceVendor_2 ON 
      Stats_2.CollegeDataSourceVendorId = CollegeDataSourceVendor_2.CollegeDataSourceVendorId
      WHERE     (dbo.CollegeDataSourceVendor.DataSourceVendorId = CollegeDataSourceVendor_2.DataSourceVendorId) AND 
      (Stats_2.StatsTypeId = dbo.Stats.StatsTypeId) AND (Stats_2.Month = @runmonth)
      GROUP BY Stats_2.StatsTypeId, CollegeDataSourceVendor_2.DataSourceVendorId
      HAVING      (Stats_2.StatsTypeId > 1)) IS NULL)

  ORDER BY dbo.View_DataSourceVendor.Source

END


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_Probable_Missing_Data_Details]
AS
SELECT     TOP (100) PERCENT dbo.Colleges.ShortName, dbo.View_DataSourceVendor.Source, dbo.StatsTypes.StatsType,
                          (SELECT     SUM(Quantity) AS Expr1
                            FROM          dbo.Stats AS Stats_1
                            WHERE      (dbo.Stats.CollegeDataSourceVendorId = CollegeDataSourceVendorId) AND (StatsTypeId = dbo.Stats.StatsTypeId) AND 
                                                   (Month = DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 2, GETDATE())), 0))
                            GROUP BY StatsTypeId, CollegeDataSourceVendorId
                            HAVING      (StatsTypeId > 1)) AS Quantity2MonthsBack
FROM         dbo.Stats INNER JOIN
                      dbo.CollegeDataSourceVendor ON dbo.Stats.CollegeDataSourceVendorId = dbo.CollegeDataSourceVendor.CollegeDataSourceVendorId INNER JOIN
                      dbo.View_DataSourceVendor ON dbo.CollegeDataSourceVendor.DataSourceVendorId = dbo.View_DataSourceVendor.DataSourceVendorId INNER JOIN
                      dbo.StatsTypes ON dbo.Stats.StatsTypeId = dbo.StatsTypes.StatsTypeId INNER JOIN
                      dbo.Colleges ON dbo.CollegeDataSourceVendor.CollegeId = dbo.Colleges.CollegeId
WHERE     (dbo.Stats.StatsTypeId > 1) AND (dbo.Stats.Month = DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 2, GETDATE())), 0))
GROUP BY dbo.View_DataSourceVendor.Source, dbo.StatsTypes.StatsType, dbo.Stats.StatsTypeId, dbo.Colleges.ShortName, 
                      dbo.Stats.CollegeDataSourceVendorId
HAVING      ((SELECT     SUM(Quantity) AS Expr1
                         FROM         dbo.Stats AS Stats_2
                         WHERE     (dbo.Stats.CollegeDataSourceVendorId = CollegeDataSourceVendorId) AND (StatsTypeId = dbo.Stats.StatsTypeId) AND 
                                                (Month = DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 1, GETDATE())), 0))
                         GROUP BY StatsTypeId, CollegeDataSourceVendorId
                         HAVING      (StatsTypeId > 1)) IS NULL)
ORDER BY dbo.Colleges.ShortName, dbo.View_DataSourceVendor.Source


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_New_AutoAdded_Datasources]
AS
SELECT     dbo.FilterTypes.FilterTypeShort, dbo.View_DataSourceVendor.Source
FROM         dbo.FilterTypes INNER JOIN
                      dbo.FilterDates ON dbo.FilterTypes.FilterTypeId = dbo.FilterDates.FilterTypeId INNER JOIN
                      dbo.View_DataSourceVendor ON dbo.FilterDates.DataSourceVendorId = dbo.View_DataSourceVendor.DataSourceVendorId
WHERE     (dbo.FilterTypes.FilterTypeShort = 'AA')


GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_New_AutoAdded_Datasources'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_DataSources_with_no_stats]
AS
SELECT     TOP (100) PERCENT dbo.View_DataSourceVendor.Source, dbo.View_DataSourceVendor.AddDate, 
                      dbo.View_DataSourceVendor.DataSourceVendorId
FROM         dbo.Stats INNER JOIN
                      dbo.CollegeDataSourceVendor ON 
                      dbo.Stats.CollegeDataSourceVendorId = dbo.CollegeDataSourceVendor.CollegeDataSourceVendorId RIGHT OUTER JOIN
                      dbo.View_DataSourceVendor ON dbo.CollegeDataSourceVendor.DataSourceVendorId = dbo.View_DataSourceVendor.DataSourceVendorId
WHERE     (dbo.CollegeDataSourceVendor.DataSourceVendorId IS NULL)
ORDER BY dbo.View_DataSourceVendor.Source


GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_DataSources_with_no_stats'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    John Nowlin
-- Create date: 10-9-8
-- Description: Retrieves searches for reports for FY, collegeid and stats type
-- =============================================
CREATE PROCEDURE [dbo].[GetFYStatsPivot] 
  -- Add the parameters for the stored procedure here
  ( @EndDateIn varchar(10), -- yyyy-mm-dd 
    @CollegeId int)
AS

BEGIN
  SET NOCOUNT ON;

-- Decide the FY start date based on enddate. FY runs 7-1 through 6-30

  DECLARE
    @FYStart SMALLDATETIME,
    @EndDate SMALLDATETIME,
    @EndYear int,
    @StartYear int
  
  SET @EndDate = CONVERT(SMALLDATETIME, @EndDateIn, 102)
  SET @EndYear = YEAR(@EndDate)
  IF MONTH(@EndDate) < 7
    BEGIN
    SET @StartYear = @EndYear - 1
    END
  ELSE
    BEGIN
    SET @StartYear = @EndYear
    END

  SET @FYStart = CONVERT(SMALLDATETIME, CAST((@StartYear) AS char(4))+'-07-01' , 102)

-- run for all colleges when 0 else run for a specific college
  IF @CollegeId = 0
    BEGIN
    SELECT *, isnull([1],0) + isnull([2],0) + isnull([3],0) + isnull([4],0) + isnull([5],0) + isnull([6],0) + isnull([7],0) + isnull([8],0) + isnull([9],0) + isnull([10],0) + isnull([11],0) + isnull([12],0) AS fytotal
      FROM (
      SELECT
        vendor, source, sw, month(month) AS monthnum, quantity
      FROM
        ReportStats
      WHERE
        Month BETWEEN @FYStart AND @EndDate
        AND statstype = 'Searches'
      )
      AS o 
      PIVOT (
        sum(Quantity)
        FOR monthnum
        IN ([7], [8], [9], [10], [11], [12], [1], [2], [3], [4], [5], [6])
      ) 
      AS p
      ORDER BY Vendor, sw, Source
    END
  ELSE
    BEGIN
      SELECT *, isnull([1],0) + isnull([2],0) + isnull([3],0) + isnull([4],0) + isnull([5],0) + isnull([6],0) + isnull([7],0) + isnull([8],0) + isnull([9],0) + isnull([10],0) + isnull([11],0) + isnull([12],0) AS fytotal
      FROM (
      SELECT
        college, vendor, source, sw, month(month) AS monthnum, quantity
      FROM
        ReportStats
      WHERE
        Month BETWEEN @FYStart AND @EndDate
        AND collegeid = @CollegeId
        AND statstype = 'Searches'
      )
      AS o 
      PIVOT (
        sum(Quantity)
        FOR monthnum
        IN ([7], [8], [9], [10], [11], [12], [1], [2], [3], [4], [5], [6])
      ) 
      AS p
      ORDER BY Vendor, sw, Source
    END

END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    John Nowlin
-- Create date: 10-9-8
-- Description: Retrieves searches for reports for FY, collegeid and stats type
-- =============================================
CREATE PROCEDURE [dbo].[GetFYeStatsPivot] 
  -- Add the parameters for the stored procedure here
  ( @EndDateIn varchar(10), -- yyyy-mm-dd 
    @CollegeId int)
AS

BEGIN
  SET NOCOUNT ON;

-- Decide the FY start date based on enddate. FY runs 7-1 through 6-30

  DECLARE
    @FYStart SMALLDATETIME,
    @EndDate SMALLDATETIME,
    @EndYear int,
    @StartYear int
  
  SET @EndDate = CONVERT(SMALLDATETIME, @EndDateIn, 102)
  SET @EndYear = YEAR(@EndDate)
  IF MONTH(@EndDate) < 7
    BEGIN
    SET @StartYear = @EndYear - 1
    END
  ELSE
    BEGIN
    SET @StartYear = @EndYear
    END

  SET @FYStart = CONVERT(SMALLDATETIME, CAST((@StartYear) AS char(4))+'-07-01' , 102)

  IF @CollegeId = 0
    BEGIN
    SELECT *, isnull([1],0) + isnull([2],0) + isnull([3],0) + isnull([4],0) + isnull([5],0) + isnull([6],0) + isnull([7],0) + isnull([8],0) + isnull([9],0) + isnull([10],0) + isnull([11],0) + isnull([12],0) AS fytotal
      FROM (
      SELECT
        vendor, sw, month(month) AS monthnum, quantity, statstype
      FROM
        ReportStats
      WHERE
        Month BETWEEN @FYStart AND @EndDate
        AND (statstypeid between 3 and 6)
      )
      AS o 
      PIVOT (
        sum(Quantity)
        FOR monthnum
        IN ([7], [8], [9], [10], [11], [12], [1], [2], [3], [4], [5], [6])
      ) 
      AS p
      ORDER BY Vendor, sw
    END
  ELSE
    BEGIN
    SELECT *, isnull([1],0) + isnull([2],0) + isnull([3],0) + isnull([4],0) + isnull([5],0) + isnull([6],0) + isnull([7],0) + isnull([8],0) + isnull([9],0) + isnull([10],0) + isnull([11],0) + isnull([12],0) AS fytotal
      FROM (
      SELECT
        vendor, source, sw, month(month) AS monthnum, quantity, statstype
      FROM
        ReportStats
      WHERE
        Month BETWEEN @FYStart AND @EndDate
        AND collegeid = @CollegeId
        AND (statstypeid between 3 and 6)
      )
      AS o 
      PIVOT (
        sum(Quantity)
        FOR monthnum
        IN ([7], [8], [9], [10], [11], [12], [1], [2], [3], [4], [5], [6])
      ) 
      AS p
      ORDER BY Vendor, sw, Source
    END

END







GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    John Nowlin
-- Create date: 10-9-8
-- Description: Retrieves searches for reports for FY, collegeid and stats type
-- =============================================
CREATE PROCEDURE [dbo].[GetFYsfxStatsPivot] 
  -- Add the parameters for the stored procedure here
  ( @EndDateIn varchar(10), -- yyyy-mm-dd 
    @CollegeId int)
AS

BEGIN
  SET NOCOUNT ON;

-- Decide the FY start date based on enddate. FY runs 7-1 through 6-30

  DECLARE
    @FYStart SMALLDATETIME,
    @EndDate SMALLDATETIME,
    @EndYear int,
    @StartYear int
  
  SET @EndDate = CONVERT(SMALLDATETIME, @EndDateIn, 102)
  SET @EndYear = YEAR(@EndDate)
  IF MONTH(@EndDate) < 7
    BEGIN
    SET @StartYear = @EndYear - 1
    END
  ELSE
    BEGIN
    SET @StartYear = @EndYear
    END

  SET @FYStart = CONVERT(SMALLDATETIME, CAST((@StartYear) AS char(4))+'-07-01' , 102)

  IF @CollegeId = 0
    BEGIN
    SELECT *, isnull([1],0) + isnull([2],0) + isnull([3],0) + isnull([4],0) + isnull([5],0) + isnull([6],0) + isnull([7],0) + isnull([8],0) + isnull([9],0) + isnull([10],0) + isnull([11],0) + isnull([12],0) AS fytotal
      FROM (
      SELECT
        vendor, sw, month(month) AS monthnum, quantity, vendor + ' ' + statstype AS statstype
      FROM
        ReportStats
      WHERE
        Month BETWEEN @FYStart AND @EndDate
        AND (statstypeid between 7 and 8 OR Vendor = 'Metalib')
      )
      AS o 
      PIVOT (
        sum(Quantity)
        FOR monthnum
        IN ([7], [8], [9], [10], [11], [12], [1], [2], [3], [4], [5], [6])
      ) 
      AS p
      ORDER BY statstype, sw
    END
  ELSE
    BEGIN
    SELECT *, isnull([1],0) + isnull([2],0) + isnull([3],0) + isnull([4],0) + isnull([5],0) + isnull([6],0) + isnull([7],0) + isnull([8],0) + isnull([9],0) + isnull([10],0) + isnull([11],0) + isnull([12],0) AS fytotal
      FROM (
      SELECT
        vendor, source, sw, month(month) AS monthnum, quantity, vendor + ' ' + statstype AS statstype
      FROM
        ReportStats
      WHERE
        Month BETWEEN @FYStart AND @EndDate
        AND collegeid = @CollegeId
        AND (statstypeid between 7 and 8 OR Vendor = 'Metalib')
      )
      AS o 
      PIVOT (
        sum(Quantity)
        FOR monthnum
        IN ([7], [8], [9], [10], [11], [12], [1], [2], [3], [4], [5], [6])
      ) 
      AS p
      ORDER BY statstype, Source
    END

END


GO
ALTER TABLE [dbo].[DataSourceVendor]  WITH CHECK ADD  CONSTRAINT [FK_DataSourceVendor_DataSources] FOREIGN KEY([DataSourceId])
REFERENCES [dbo].[DataSources] ([DataSourceId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DataSourceVendor] CHECK CONSTRAINT [FK_DataSourceVendor_DataSources]
GO
ALTER TABLE [dbo].[DataSourceVendor]  WITH CHECK ADD  CONSTRAINT [FK_DataSourceVendor_Vendors] FOREIGN KEY([VendorId])
REFERENCES [dbo].[Vendors] ([VendorId])
GO
ALTER TABLE [dbo].[DataSourceVendor] CHECK CONSTRAINT [FK_DataSourceVendor_Vendors]
GO
ALTER TABLE [dbo].[LoginInformation]  WITH CHECK ADD  CONSTRAINT [FK_CollegeVendor_Colleges] FOREIGN KEY([CollegeId])
REFERENCES [dbo].[Colleges] ([CollegeId])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[LoginInformation] CHECK CONSTRAINT [FK_CollegeVendor_Colleges]
GO
ALTER TABLE [dbo].[LoginInformation]  WITH CHECK ADD  CONSTRAINT [FK_CollegeVendor_Vendors] FOREIGN KEY([VendorId])
REFERENCES [dbo].[Vendors] ([VendorId])
GO
ALTER TABLE [dbo].[LoginInformation] CHECK CONSTRAINT [FK_CollegeVendor_Vendors]
GO
ALTER TABLE [dbo].[FilterDates]  WITH CHECK ADD  CONSTRAINT [FK_FilterDates_DataSourceVendor] FOREIGN KEY([DataSourceVendorId])
REFERENCES [dbo].[DataSourceVendor] ([DataSourceVendorId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FilterDates] CHECK CONSTRAINT [FK_FilterDates_DataSourceVendor]
GO
ALTER TABLE [dbo].[FilterDates]  WITH CHECK ADD  CONSTRAINT [FK_FilterDates_FilterTypes] FOREIGN KEY([FilterTypeId])
REFERENCES [dbo].[FilterTypes] ([FilterTypeId])
GO
ALTER TABLE [dbo].[FilterDates] CHECK CONSTRAINT [FK_FilterDates_FilterTypes]
GO
ALTER TABLE [dbo].[CollegeDataSourceVendor]  WITH CHECK ADD  CONSTRAINT [FK_CollegeDataSourceVendor_Colleges] FOREIGN KEY([CollegeId])
REFERENCES [dbo].[Colleges] ([CollegeId])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[CollegeDataSourceVendor] CHECK CONSTRAINT [FK_CollegeDataSourceVendor_Colleges]
GO
ALTER TABLE [dbo].[CollegeDataSourceVendor]  WITH CHECK ADD  CONSTRAINT [FK_CollegeDataSourceVendor_DataSourceVendor] FOREIGN KEY([DataSourceVendorId])
REFERENCES [dbo].[DataSourceVendor] ([DataSourceVendorId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CollegeDataSourceVendor] CHECK CONSTRAINT [FK_CollegeDataSourceVendor_DataSourceVendor]
GO
ALTER TABLE [dbo].[Stats]  WITH CHECK ADD  CONSTRAINT [FK_Stats_CollegeDataSourceVendor] FOREIGN KEY([CollegeDataSourceVendorId])
REFERENCES [dbo].[CollegeDataSourceVendor] ([CollegeDataSourceVendorId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Stats] CHECK CONSTRAINT [FK_Stats_CollegeDataSourceVendor]

GO
INSERT INTO [dbo].[FilterTypes] ([FilterTypeId],[FilterType],[FilterTypeShort]) VALUES (0,'College Owned','');
INSERT INTO [dbo].[FilterTypes] ([FilterTypeId],[FilterType],[FilterTypeShort]) VALUES (1,'Statewide','SW');
INSERT INTO [dbo].[FilterTypes] ([FilterTypeId],[FilterType],[FilterTypeShort]) VALUES (2,'Managed','MA');
INSERT INTO [dbo].[FilterTypes] ([FilterTypeId],[FilterType],[FilterTypeShort]) VALUES (3,'Suppresed','SU');
INSERT INTO [dbo].[FilterTypes] ([FilterTypeId],[FilterType],[FilterTypeShort]) VALUES (4,'Trial','TR');
INSERT INTO [dbo].[StatsTypes] ([StatsType]) VALUES ('Sessions');
INSERT INTO [dbo].[StatsTypes] ([StatsType]) VALUES ('Searches');
INSERT INTO [dbo].[StatsTypes] ([StatsType]) VALUES ('eBooks');
INSERT INTO [dbo].[StatsTypes] ([StatsType]) VALUES ('eAudio');
INSERT INTO [dbo].[StatsTypes] ([StatsType]) VALUES ('eJournal');
INSERT INTO [dbo].[StatsTypes] ([StatsType]) VALUES ('eVideo');
INSERT INTO [dbo].[StatsTypes] ([StatsType]) VALUES ('Requests');
INSERT INTO [dbo].[StatsTypes] ([StatsType]) VALUES ('Clickthroughs');
